package demo;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelDiffComparator {

    public static void compare(File file1, File file2, List<String> primaryKeys, File outputDir, String reportDiffPrefix) throws Exception {
        String outputPath = outputDir.getAbsolutePath();

        List<Map<String, String>> dataList1 = ExcelReaderUtil.readExcelAsMapByHeader(file1);
        List<Map<String, String>> dataList2 = ExcelReaderUtil.readExcelAsMapByHeader(file2);

        List<String> headers1 = getHeaderFields(dataList1);
        List<String> headers2 = getHeaderFields(dataList2);

        Set<String> onlyIn1 = new LinkedHashSet<>(headers1);
        onlyIn1.removeAll(headers2);

        Set<String> onlyIn2 = new LinkedHashSet<>(headers2);
        onlyIn2.removeAll(headers1);

        Set<String> commonFields = new LinkedHashSet<>(headers1);
        commonFields.retainAll(headers2);

        if (!onlyIn1.isEmpty() || !onlyIn2.isEmpty()) {
            System.out.println("⚠️ 表头字段不一致：");
            if (!onlyIn1.isEmpty()) {
                System.out.println("  ✅ A 文件独有字段: " + String.join(", ", onlyIn1));
            }
            if (!onlyIn2.isEmpty()) {
                System.out.println("  ✅ B 文件独有字段: " + String.join(", ", onlyIn2));
            }
            System.out.println("👉 将仅对以下字段进行内容对比: " + String.join(", ", commonFields));
        }

        Map<String, Map<String, String>> map1 = buildKeyedMap(dataList1, primaryKeys);
        Map<String, Map<String, String>> map2 = buildKeyedMap(dataList2, primaryKeys);

        List<DiffResult> diffResults = new ArrayList<>();

        // A 文件中存在但 B 不存在
        for (String key : map1.keySet()) {
            if (!map2.containsKey(key)) {
                diffResults.add(new DiffResult(
                        key, "only_in_a", "-", "存在", "缺失", "B 文件中缺失该主键记录"
                ));
            }
        }

        // B 文件中存在但 A 不存在
        for (String key : map2.keySet()) {
            if (!map1.containsKey(key)) {
                diffResults.add(new DiffResult(
                        key, "only_in_b", "-", "缺失", "存在", "A 文件中缺失该主键记录"
                ));
            }
        }

        // 内容差异比对
        for (String key : map1.keySet()) {
            Map<String, String> row1 = map1.get(key);
            Map<String, String> row2 = map2.get(key);
            if (row2 == null) continue;

            for (String field : commonFields) {
                String val1 = StringUtils.defaultString(row1.get(field));
                String val2 = StringUtils.defaultString(row2.get(field));

                if (Objects.equals(val1, val2)) continue;

                boolean bothNumeric = isNumeric(val1) && isNumeric(val2);
                boolean oneEmptyOtherNumber = (StringUtils.isBlank(val1) && isNumeric(val2)) || (StringUtils.isBlank(val2) && isNumeric(val1));

                String diffType;
                String diffDetail;

                if (bothNumeric || oneEmptyOtherNumber) {
                    double d1 = StringUtils.isBlank(val1) ? 0.0 : Double.parseDouble(val1);
                    double d2 = StringUtils.isBlank(val2) ? 0.0 : Double.parseDouble(val2);
                    double diff = d2 - d1;

                    if (Math.abs(diff) < 1e-4) continue;

                    diffType = "value_diff";
                    diffDetail = String.format("%.4f", diff);
                } else {
                    diffType = "text_diff";
                    diffDetail = "[" + val1 + "] -> [" + val2 + "]";
                }

                diffResults.add(new DiffResult(
                        key, diffType, field, val1, val2, diffDetail
                ));
            }
        }

        // 写出 Excel 报告
//        String filename = "diff_report_" + reportDiffPrefix + "_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".xlsx";
//        String fullPath = outputPath + File.separator + filename;
//        Files.createDirectories(Paths.get(outputPath));
//
//        try (ExcelWriter writer = EasyExcel.write(fullPath, DiffResult.class).build()) {
//            WriteSheet sheet = EasyExcel.writerSheet("差异报告").build();
//            writer.write(diffResults, sheet);
//        }
//                System.out.println("✅ 差异报告已生成: " + fullPath);

        // 写出 Excel 报告（分多个文件处理大数据量）
        int batchSize = 500000; // 每个文件最多50万条数据
        int totalRecords = diffResults.size();
        int fileCount = (int) Math.ceil((double) totalRecords / batchSize);

        for (int i = 0; i < fileCount; i++) {
            // 计算当前批次的起始和结束索引
            int fromIndex = i * batchSize;
            int toIndex = Math.min((i + 1) * batchSize, totalRecords);
            List<DiffResult> batch = diffResults.subList(fromIndex, toIndex);

            // 生成带序号的文件名
            String filename = "diff_report_" + reportDiffPrefix + "_" +
                    new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) +
                    "_part" + (i + 1) + ".xlsx";
            String fullPath = outputPath + File.separator + filename;

            // 确保输出目录存在
            Files.createDirectories(Paths.get(outputPath));
            System.out.println("✅ 差异报告已生成: " + fullPath);
            try (ExcelWriter writer = EasyExcel.write(fullPath, DiffResult.class).build()) {
                WriteSheet sheet = EasyExcel.writerSheet("差异报告").build();
                writer.write(batch, sheet);
            }
        }


    }

    private static List<String> getHeaderFields(List<Map<String, String>> dataList) {
        return dataList.isEmpty() ? Collections.emptyList() : new ArrayList<>(dataList.get(0).keySet());
    }

    private static Map<String, Map<String, String>> buildKeyedMap(List<Map<String, String>> dataList, List<String> primaryKeys) {
        Map<String, Map<String, String>> result = new LinkedHashMap<>();
        Set<String> duplicates = new HashSet<>();
        for (Map<String, String> row : dataList) {
            String key = primaryKeys.stream().map(k -> row.getOrDefault(k, "")).collect(Collectors.joining("#"));
            if (result.containsKey(key)) {
                duplicates.add(key);
            } else {
                result.put(key, row);
            }
        }
        if (!duplicates.isEmpty()) {
            throw new RuntimeException("唯一键不唯一，请检查维度：" + String.join(", ", duplicates));
        }
        return result;
    }

    private static boolean isNumeric(String str) {
        if (StringUtils.isBlank(str)) return false;
        try {
            Double.parseDouble(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class DiffResult {
        private String primaryKey;
        private String diffType;
        private String fieldName;
        private String file1Value;
        private String file2Value;
        private String diffDetail;
    }
}